/**
 * 获取分类列表API
 * 从数据库中查询所有分类信息，支持排除指定的分类ID
 */

import type { ToolTab } from '~/types/tools'
import { createDatabaseConnection } from '../config/database'

/**
 * 处理获取分类列表的请求
 * 支持通过excludeIds参数排除指定的分类
 */
export default defineEventHandler(async (event) => {
  try {
    // 获取查询参数
    const query = getQuery(event)
    const excludeIds = query.excludeIds ? (query.excludeIds as string).split(',').map(id => parseInt(id)) : []

    // 创建数据库连接
    const connection = await createDatabaseConnection()

    // 构建SQL查询
    let sql = `
      SELECT 
        tab_id,
        title,
        description,
        banner_path
      FROM ai_tools_tab
      WHERE 1=1
    `
    
    // 如果有需要排除的分类ID，添加到查询条件中
    if (excludeIds.length > 0) {
      sql += ` AND tab_id NOT IN (${excludeIds.map(() => '?').join(',')})`
    }
    
    sql += ` ORDER BY tab_id ASC`

    // 执行查询
    // eslint-disable-next-line @typescript-eslint/ban-ts-comment
    // @ts-expect-error
    const [result] = await connection.execute(sql, excludeIds.length > 0 ? excludeIds : [])
    
    // 关闭数据库连接
    await connection.end()

    // 处理查询结果
    const categories: ToolTab[] = (result as { tab_id: number; category_id: number; title: string; description: string; banner_path: string }[]).map(row => ({
      categoryId: row.tab_id,
      title: row.title,
      description: row.description,
      bannerPath: row.banner_path
    }))

    // 返回成功响应
    return {
      success: true,
      data: categories,
      total: categories.length
    }
  } catch (error) {
    // 错误处理：记录错误日志并返回500错误
    console.error('Database query error:', error)
    throw createError({
      statusCode: 500,
      statusMessage: '数据库查询失败'
    })
  }
})